using System;
using System.Collections.Generic;
using System.Text;
using DAL;
using DTO;
using System.Data;
using System.Data.OleDb;
namespace BLL
{
    public class SinhVienDAL
    {
                public List<SINHVIEN> GetAll()
                {
                    SINHVIEN sv;
                    List<SINHVIEN> list = new List<SINHVIEN>();
                    DataProvider dataprovider = new DataProvider();
                    DataTable datatable = new DataTable();
                    datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN,KHOA WHere KHoa.MAkhoa=SINHVIEN.MAKHOA ", null);
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        sv = new SINHVIEN();
                        sv.MaSV = datatable.Rows[i]["MaSV"].ToString();
                        sv.Ten = datatable.Rows[i]["Ten"].ToString();
                        sv.Holot = datatable.Rows[i]["Holot"].ToString();
                        sv.Makhoa = Convert.ToByte (datatable.Rows[i]["SINHVIEN.MAKHOA"]);
                        sv.Tenkhoa = datatable.Rows[i]["TENKHOA"].ToString();
                        list.Add(sv);
                    }
                    return list;
                }

        public SINHVIEN GetSINHVIEN(string masv)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN where MASV = @masv", parameters);
            SINHVIEN sv = new SINHVIEN();
            if (datatable.Rows.Count != 0)
            {
                sv.MaSV = datatable.Rows[0]["Masv"].ToString();
                sv.Ten = datatable.Rows[0]["Ten"].ToString();
                sv.Holot = datatable.Rows[0]["Holot"].ToString();
                sv.Makhoa = Convert.ToByte( datatable.Rows[0]["makhoa"]);
                return sv;
            }
            else
                return null;
        }
        public bool UpdateSV(string masv, string ten, string holot, int makhoa, string masvcu  )
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[5];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            parameters[1] = new OleDbParameter("@tensv", OleDbType.Char);
            parameters[1].Value = ten;
            parameters[2] = new OleDbParameter("@holot", OleDbType.Char);
            parameters[2].Value = holot;
            parameters[3] = new OleDbParameter("@makhoa", OleDbType.Char);
            parameters[3].Value = makhoa;
           // parameters[4] = new OleDbParameter("@masvcu", OleDbType.Char);
           // parameters[4].Value = masvcu;
            return dataprovider.ExecuteNonSelectQuery("update SINHVIEN set MaSV=@masv, MaKhoa=@makhoa, Ten=@ten, HOLOT=@holot where MaSV = @masvcu", parameters);
        }
    }
}
